package com.sui.bigdata.flink.sql.side;

import com.sui.bigdata.flink.sql.core.Main;
import org.apache.flink.calcite.shaded.com.google.common.base.Charsets;
import org.apache.flink.calcite.shaded.com.google.common.collect.Lists;
import org.junit.Test;

import java.net.URLEncoder;
import java.util.List;

/**
 * @author YongChen
 * @date 2020/5/18 17:05
 * @description
 * @email yong_chen@sui.com
 */
public class pingfenka20 {
    @Test
    public void test1() throws Exception {
        String sql = "CREATE TABLE behaviourPortrayal_bdl_lyy_target_operation_variable (\n" +
                "\tsuid varchar as suid\n" +
                ") WITH (\n" +
                "    type = 'kafka',\n" +
                "    bootstrapServers = '10.201.7.187:9093,10.201.7.188:9093',\n" +
                "    offsetReset = 'latest',\n" +
                "    groupId = 'device_behaviour_portrayal_fkfqz_txmx_1218_y',\n" +
                "    topic = 'datateam.bdl_lyy_target_operation_variable_fkfqzmodel_middle',\n" +
                "    parallelism = '1'\n" +
                ");\n" +
                "\n" +
                "CREATE TABLE behaviourPortrayal_adl_bd_lyy_target_operation_variable_t (\n" +
                "    suid VARCHAR,\n" +
                "    page_code VARCHAR,\n" +
                "    standard_field VARCHAR,\n" +
                "\toperation_duration double,\n" +
                "\toperation_count double,\n" +
                "    PRIMARY KEY (suid),\n" +
                "    PERIOD FOR SYSTEM_TIME\n" +
                ") WITH (\n" +
                "    type = 'mysql',\n" +
                "    url = 'jdbc:mysql://tidb1.testfeideedba.com:4000/dt?useUnicode=true&amp;characterEncoding=utf8;useSSL=true',\n" +
                "    userName = 'DSJ_user',\n" +
                "    PASSWORD = '606cde3507b026H,dHb_77e67H_e',\n" +
                "    tableName = 'adl_bd_lyy_target_operation_variable_t',\t\n" +
                "\tparallelism ='1'\n" +
                ");\n" +
                "\n" +
                "\n" +
                "select \n" +
                "    suid,\n" +
                "\tmd5(suid) as suid_md5,\n" +
                "    (case when information_secondcontactpersonname_duration <> 'NULL' then cast(round(information_secondcontactpersonname_duration,2) as varchar) else '-1' end) as information_secondcontactpersonname_duration,\n" +
                "\t(case when information_secondcontactpersontel_duration <> 'NULL' then cast(round(information_secondcontactpersontel_duration,2) as varchar) else '-1' end)  as information_secondcontactpersontel_duration,\n" +
                "\t(case when information_companyaddress_duration <> 'NULL' then cast(round(information_companyaddress_duration,2) as varchar) else '-1' end) as information_companyaddress_duration,\n" +
                "\t(case when information_companytel_duration <> 'NULL' then cast(round(information_companytel_duration,2) as varchar) else '-1' end) as information_companytel_duration,\n" +
                "\t(case when information_livingarea_duration <> 'NULL' then cast(round(information_livingarea_duration,2) as varchar) else '-1' end) as information_livingarea_duration,\n" +
                "\t(case when information_firstcontactpersontel_duration <> 'NULL' then cast(round(information_firstcontactpersontel_duration,2) as varchar) else '-1' end) as information_firstcontactpersontel_duration,\n" +
                "\t(case when information_submit_duration <> 'NULL' then cast(round(information_submit_duration,2) as varchar) else '-1' end) as information_submit_duration,\n" +
                "\t(case when realName_idCardImgBack_duration <> 'NULL' then cast(round(realName_idCardImgBack_duration,2) as varchar) else '-1' end) as realName_idCardImgBack_duration,\n" +
                "\t(case when realname_idcardimgfront_duration <> 'NULL' then cast(round(realname_idcardimgfront_duration,2) as varchar) else '-1' end) as realname_idcardimgfront_duration,\n" +
                "\t(case when bindphone_verifycode_duration <> 'NULL' then cast(round(bindphone_verifycode_duration,2) as varchar) else '-1' end) as bindphone_verifycode_duration,\n" +
                "\t(case when bindphone_submit_avg <> 'NULL' then cast(round(bindphone_submit_avg,2) as varchar) else '-1' end) as bindphone_submit_avg,\n" +
                "\t(case when authlist_submit_duration <> 'NULL' then cast(round(authlist_submit_duration,2) as varchar) else '-1' end) as authlist_submit_duration,\n" +
                "\t(case when withdraw_loanpurpose_duration <> 'NULL' then cast(round(withdraw_loanpurpose_duration,2) as varchar) else '-1' end) as withdraw_loanpurpose_duration\n" +
                "from (\n" +
                "select \n" +
                "    suid, \n" +
                "    avg(case when page_code = 'information' and standard_field = 'secondContactPersonName' then operation_duration end) as information_secondcontactpersonname_duration,\n" +
                "    avg(case when page_code = 'information' and standard_field = 'secondContactPersonTel' then operation_duration end) as information_secondcontactpersontel_duration,\n" +
                "    avg(case when page_code = 'information' and standard_field = 'companyAddress' then operation_duration end) as information_companyaddress_duration,\n" +
                "    avg(case when page_code = 'information' and standard_field = 'companyTel' then operation_duration end) as information_companytel_duration,\n" +
                "    avg(case when page_code = 'information' and standard_field = 'livingArea' then operation_duration end) as information_livingarea_duration,\n" +
                "    avg(case when page_code = 'information' and standard_field = 'firstContactPersonTel' then operation_duration end) as information_firstcontactpersontel_duration,\n" +
                "    avg(case when page_code = 'information' and standard_field = 'submit' then operation_duration end) as information_submit_duration,\n" +
                "    avg(case when page_code = 'realName' and standard_field = 'idCardImgBack' then operation_duration end) as realName_idCardImgBack_duration,\n" +
                "    avg(case when page_code = 'realName' and standard_field = 'idCardImgFront' then operation_duration end) as realname_idcardimgfront_duration,\n" +
                "    avg(case when page_code = 'bindPhone' and standard_field = 'verifyCode' then operation_duration end) as bindphone_verifycode_duration,\n" +
                "    avg(case when page_code = 'bindPhone' and standard_field = 'submit' then operation_count end) as bindphone_submit_avg,\n" +
                "    avg(case when page_code = 'authList' and standard_field = 'submit' then operation_duration end) as authlist_submit_duration,\n" +
                "    avg(case when page_code = 'withdraw' and standard_field = 'loanPurpose' then operation_duration end) as withdraw_loanpurpose_duration\n" +
                "from \n" +
                "  (select \n" +
                "    kfk.proctime as proctime,\n" +
                "\t kfk.suid as suid,\n" +
                "\t tdb.page_code as page_code,\n" +
                "\t tdb.standard_field as standard_field,\n" +
                "\t tdb.operation_duration as operation_duration,\n" +
                "\t tdb.operation_count as operation_count\n" +
                "  from behaviourPortrayal_bdl_lyy_target_operation_variable kfk\n" +
                "  join behaviourPortrayal_adl_bd_lyy_target_operation_variable_t tdb\n" +
                "  on kfk.suid = tdb.suid and tdb.operation_duration>0 and tdb.operation_count>0) t\n" +
                "GROUP BY SESSION(t.proctime, INTERVAL '1' SECOND),t.suid) alltag;\n" ;


        exec(sql);
    }

    @Test
    public void test2() throws Exception {
        String sql ="\n" +
                " ---etl\n" +
                " \n" +
                " CREATE TABLE bdl_suigo_loan_customer_info(\n" +
                "  rowData.id varchar as  customer_id,\n" +
                "  rowData.ssj_name varchar as suid\n" +
                " )WITH(\n" +
                "    type ='kafka',\n" +
                "    bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "    offsetReset ='latest',\n" +
                "    groupId = 'bdl_suigo_loan_customer_info_20200518',\n" +
                "    topic ='rtsync.bdl_suigo_loan_customer_info',\n" +
                "    parallelism ='1'\n" +
                " );\n" +
                " \n" +
                " \n" +
                "CREATE TABLE dws_dim_ssj_user(\n" +
                "  fname varchar as  fname,\n" +
                "  register_time varchar as  register_time\n" +
                " )WITH(\n" +
                "    type ='kafka',\n" +
                "    bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "    offsetReset ='latest',\n" +
                "    groupId = 'dws_dim_ssj_user_20200518',\n" +
                "    topic ='rthbase.dws_dim_ssj_user',\n" +
                "    parallelism ='1'\n" +
                " ); \n" +
                " \n" +
                "CREATE TABLE bdl_suigo_loan_customer_info_result(\n" +
                "     info:customer_id    varchar,\n" +
                "     info:suid       varchar\n" +
                "    )WITH(\n" +
                "     type='hbase',\n" +
                "     zookeeperQuorum='10.201.7.113:2181,10.201.7.114:2181,10.201.7.115:2181',\n" +
                "     zookeeperParent = '/hbase',\n" +
                "     tableName='adl_bd_ls_risk_control_middle_table',\n" +
                "     rowkey='info:id'\n" +
                "); \n" +
                "\n" +
                "CREATE TABLE customer_per_base_info_result1(\n" +
                "     info:fname  varchar,\n" +
                "     info:register_time    varchar\n" +
                "    )WITH(\n" +
                "     type='hbase',\n" +
                "     zookeeperQuorum='10.201.7.113:2181,10.201.7.114:2181,10.201.7.115:2181',\n" +
                "     zookeeperParent = '/hbase',\n" +
                "     tableName='adl_bd_ls_risk_control_middle_table',\n" +
                "     rowkey='info:fname'\n" +
                "); \n" +
                " \n" +
                " \n" +
                "insert into  bdl_suigo_loan_customer_info_result\n" +
                "select md5(customer_id) as customer_id_md5,suid\n" +
                "from bdl_suigo_loan_customer_info\n" +
                "where NotBlank(customer_id);\n" +
                "\n" +
                "insert into  customer_per_base_info_result1\n" +
                "select md5(fname) as fname,register_time \n" +
                "from dws_dim_ssj_user\n" +
                "where NotBlank(fname);\n" +
                " \n" +
                "---etl \n" +
                "create scalar function NotBlank with com.sui.bigdata.flink.sql.function.scalar.NotBlank;\n" +
                "CREATE scalar function FinAesDecode with com.sui.bigdata.flink.sql.function.scalar.FinAesDecode;\n" +
                "\n" +
                "CREATE TABLE bdl_suigo_loan_customer_app_active(\n" +
                "  rowData.client_uname varchar as suid,\n" +
                "  rowData.information_certid_no varchar as  id_num,\n" +
                "  rowData.actived_time varchar as actived_time,\n" +
                "  rowData.active_gps_city varchar as active_gps_city,\n" +
                "  rowData.active_gps_prov varchar as active_gps_prov\n" +
                " )WITH(\n" +
                "    type ='kafka',\n" +
                "    bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "    offsetReset ='latest',\n" +
                "    groupId = 'bdl_suigo_loan_customer_app_active_20200518',\n" +
                "    topic ='rtsync.bdl_suigo_loan_customer_app_active',\n" +
                "    parallelism ='1'\n" +
                " ); \n" +
                " \n" +
                " \n" +
                "CREATE TABLE bdl_suigo_loan_customer_per_base_info(\n" +
                "  rowData.customer_id varchar  as customer_id,\n" +
                "  rowData.edu_level varchar as  edu_level\n" +
                " )WITH(\n" +
                "    type ='kafka',\n" +
                "    bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "    offsetReset ='latest',\n" +
                "    groupId = 'bdl_suigo_loan_customer_per_base_info_20200518',\n" +
                "    topic ='rtsync.bdl_suigo_loan_customer_per_base_info',\n" +
                "    parallelism ='1'\n" +
                " ); \n" +
                " \n" +
                " \n" +
                " CREATE TABLE bdl_nova_t_struct_bingjian_multi(\n" +
                "  rowData.serial_no               varchar as  serial_no,\n" +
                "  rowData.app_mass_high_num_m1    varchar as  app_mass_high_num_m1,\n" +
                "  rowData.app_mass_high_num_m3    varchar as  app_mass_high_num_m3,\n" +
                "  rowData.app_mass_high_num_m12   varchar as  app_mass_high_num_m12,\n" +
                "  rowData.app_use_months_m6       varchar as  app_use_months_m6,\n" +
                "  rowData.app_use_num_m1          varchar as  app_use_num_m1,\n" +
                "  rowData.app_use_num_m3          varchar as  app_use_num_m3,\n" +
                "  rowData.app_use_num_m6          varchar as  app_use_num_m6,\n" +
                "  rowData.app_use_num_m12         varchar as  app_use_num_m12\n" +
                " )WITH(\n" +
                "    type ='kafka',\n" +
                "    bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "    offsetReset ='latest',\n" +
                "    groupId = 'bdl_nova_t_struct_bingjian_multi_20200518',\n" +
                "    topic ='rtsync.bdl_nova_t_struct_bingjian_multi',\n" +
                "    parallelism ='1'\n" +
                " ); \n" +
                " \n" +
                " \n" +
                "  CREATE TABLE bdl_nova_t_struct_bingjian_clever_eyes_score(\n" +
                "  rowData.serial_no     varchar as  serial_no,\n" +
                "  rowData.bj_hyscore    varchar as  bj_hyscore\n" +
                " )WITH(\n" +
                "    type ='kafka',\n" +
                "    bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "    offsetReset ='latest',\n" +
                "    groupId = 'bdl_nova_t_struct_bingjian_clever_eyes_score_20200518',\n" +
                "    topic ='rtsync.bdl_nova_t_struct_bingjian_clever_eyes_score',\n" +
                "    parallelism ='1'\n" +
                " ); \n" +
                " \n" +
                " \n" +
                "CREATE TABLE bdl_nova_t_struct_tencent_antifraud(\n" +
                "  rowData.serial_no    varchar as  serial_no,\n" +
                "  rowData.tx_score1    varchar as  tx_score1,\n" +
                "  rowData.tx_score2    varchar as  tx_score2,\n" +
                "  rowData.tx_score3    varchar as  tx_score3,\n" +
                "  rowData.tx_score4    varchar as  tx_score4,\n" +
                "  rowData.tx_score5    varchar as  tx_score5,\n" +
                "  rowData.tx_score6    varchar as  tx_score6\n" +
                " )WITH(\n" +
                "    type ='kafka',\n" +
                "    bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "    offsetReset ='latest',\n" +
                "    groupId = 'bdl_nova_t_struct_tencent_antifraud_20200518',\n" +
                "    topic ='rtsync.bdl_nova_t_struct_tencent_antifraud',\n" +
                "    parallelism ='1'\n" +
                " );\n" +
                "\n" +
                " \n" +
                "CREATE TABLE taga(\n" +
                "     rowkey        varchar,\n" +
                "     cf            varchar,\n" +
                "     hbase_table   varchar,\n" +
                "     suid          varchar,\n" +
                "\t age           int,\n" +
                "\t sex           varchar,\n" +
                "\t ssj_u         varchar,\n" +
                "     a_g_c_qth     varchar,\n" +
                "\t a_g_c_sm      varchar,\n" +
                "\t a_g_c_dys     varchar,\n" +
                "\t a_g_c_zs      varchar,\n" +
                "\t a_g_c_fs      varchar,\n" +
                "\t a_g_c_bd      varchar,\n" +
                "\t a_g_c_bj      varchar,\n" +
                "\t a_g_c_nj      varchar,\n" +
                "\t a_g_c_pn      varchar,\n" +
                "\t a_g_c_np      varchar,\n" +
                "\t a_g_c_nc      varchar,\n" +
                "\t a_g_c_nt      varchar,\n" +
                "\t a_g_c_xm      varchar,\n" +
                "\t a_g_c_tzs     varchar,\n" +
                "\t a_g_c_jl      varchar,\n" +
                "\t a_g_c_wzs     varchar,\n" +
                "\t a_g_c_hlbe    varchar,\n" +
                "\t a_g_c_xn      varchar,\n" +
                "\t a_g_c_jx      varchar,\n" +
                "\t a_g_c_dlbz    varchar,\n" +
                "\t a_g_c_dl      varchar,\n" +
                "\t a_g_c_ty      varchar,\n" +
                "\t a_g_c_nb      varchar,\n" +
                "\t a_g_c_yc      varchar,\n" +
                "\t a_g_c_yy      varchar,\n" +
                "\t a_g_c_cz      varchar,\n" +
                "\t a_g_c_lf      varchar,\n" +
                "\t a_g_c_dy      varchar,\n" +
                "\t a_g_c_yz      varchar,\n" +
                "\t a_g_c_jys     varchar,\n" +
                "\t a_g_c_wx      varchar,\n" +
                "\t a_g_c_hz      varchar,\n" +
                "\t a_g_c_lz      varchar,\n" +
                "\t a_g_c_wh      varchar,\n" +
                "\t a_g_c_sy      varchar,\n" +
                "\t a_g_c_qz      varchar,\n" +
                "\t a_g_c_ta      varchar,\n" +
                "\t a_g_c_tz      varchar,\n" +
                "\t a_g_c_ly      varchar,\n" +
                "\t a_g_c_jn      varchar,\n" +
                "\t a_g_c_jy      varchar,\n" +
                "\t a_g_c_hk      varchar,\n" +
                "\t a_g_c_zb      varchar,\n" +
                "\t a_g_c_hbss    varchar,\n" +
                "\t a_g_c_wn      varchar,\n" +
                "\t a_g_c_hzs     varchar,\n" +
                "\t a_g_c_xts     varchar,\n" +
                "\t a_g_c_xxtj    varchar,\n" +
                "\t a_g_c_zjss    varchar,\n" +
                "\t a_g_c_xzs     varchar,\n" +
                "\t a_g_c_bzs     varchar,\n" +
                "\t a_g_c_zzss    varchar\n" +
                ")WITH(\n" +
                "     type ='kafka',\n" +
                "     bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "     topic ='realtime_risk_ctrl_tag',\n" +
                "     parallelism ='1'\n" +
                "  ); \n" +
                "  \n" +
                "CREATE TABLE tagb(\n" +
                "     rowkey        varchar,\n" +
                "     cf            varchar,\n" +
                "     hbase_table   varchar,\n" +
                "     suid          varchar,  \n" +
                "\t a_g_c_wfs     varchar,\n" +
                "\t a_g_c_czss    varchar,\n" +
                "\t a_g_c_pys     varchar,\n" +
                "\t a_g_c_ytss    varchar,\n" +
                "\t a_g_c_mdj     varchar,\n" +
                "\t a_g_c_zhs     varchar,\n" +
                "\t a_g_c_mms     varchar,\n" +
                "\t a_g_c_hyss    varchar,\n" +
                "\t a_g_c_zyss    varchar,\n" +
                "\t a_g_c_gzs     varchar,\n" +
                "\t a_g_c_dqzz    varchar,\n" +
                "\t a_g_c_sns     varchar,\n" +
                "\t a_g_c_zys     varchar,\n" +
                "\t a_g_c_sys     varchar,\n" +
                "\t a_g_c_zzs     varchar,\n" +
                "\t a_g_c_eeds    varchar,\n" +
                "\t a_g_c_jqs     varchar,\n" +
                "\t a_g_c_jhs     varchar,\n" +
                "\t a_g_c_jb      varchar,\n" +
                "\t a_g_c_qzs     varchar,\n" +
                "\t a_g_c_tls     varchar,\n" +
                "\t a_g_c_trs     varchar,\n" +
                "\t a_g_c_ycs     varchar,\n" +
                "\t a_g_c_xls     varchar,\n" +
                "\t a_g_c_jxss    varchar,\n" +
                "\t a_g_c_zjs     varchar,\n" +
                "\t a_g_c_ccs     varchar,\n" +
                "\t a_g_c_cc      varchar,\n" +
                "\t a_g_c_czs     varchar,\n" +
                "\t a_g_c_hxx     varchar,\n" +
                "\t a_g_c_hys     varchar,\n" +
                "\t a_g_c_fcg     varchar,\n" +
                "\t a_g_c_yjs     varchar,\n" +
                "\t a_g_c_yqs     varchar,\n" +
                "\t a_g_c_abzz    varchar,\n" +
                "\t a_g_c_alsm    varchar,\n" +
                "\t a_g_c_lns     varchar,\n" +
                "\t a_g_c_szss    varchar,\n" +
                "\t a_g_c_yas     varchar,\n" +
                "\t a_g_c_qds     varchar,\n" +
                "\t a_g_c_ass     varchar,\n" +
                "\t a_g_c_sgs     varchar,\n" +
                "\t a_g_c_mass    varchar,\n" +
                "\t a_g_c_zmds    varchar,\n" +
                "\t a_g_c_jxs     varchar,\n" +
                "\t a_g_c_hbsss   varchar,\n" +
                "\t a_g_c_hgss    varchar,\n" +
                "\t a_g_c_yts     varchar\n" +
                ")WITH(\n" +
                "     type ='kafka',\n" +
                "     bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "     topic ='realtime_risk_ctrl_tag',\n" +
                "     parallelism ='1'\n" +
                "  );   \n" +
                "  \n" +
                "  \n" +
                "CREATE TABLE tagc(\n" +
                "     rowkey        varchar,\n" +
                "     cf            varchar,\n" +
                "     hbase_table   varchar,\n" +
                "     suid          varchar,\n" +
                "\t a_g_c_hgs     varchar,\n" +
                "\t a_g_c_hss     varchar,\n" +
                "\t a_g_c_hsss    varchar,\n" +
                "\t a_g_c_hhs     varchar,\n" +
                "\t a_g_c_qdnmz   varchar,\n" +
                "\t a_g_c_qdnyz   varchar,\n" +
                "\t a_g_c_qxnby   varchar,\n" +
                "\t a_g_c_qqhe    varchar,\n" +
                "\t a_g_c_lys     varchar,\n" +
                "\t a_g_c_sh      varchar,\n" +
                "\t a_g_c_dg      varchar,\n" +
                "\t a_g_c_nj      varchar,\n" +
                "\t a_g_c_hf      varchar,\n" +
                "\t a_g_c_tj      varchar,\n" +
                "\t a_g_c_gz      varchar,\n" +
                "\t a_g_c_cd      varchar,\n" +
                "\t a_g_c_km      varchar,\n" +
                "\t a_g_c_sz      varchar,\n" +
                "\t a_g_c_wz      varchar,\n" +
                "\t a_g_c_sjz     varchar,\n" +
                "\t a_g_c_fz      varchar,\n" +
                "\t a_g_c_szs     varchar,\n" +
                "\t a_g_c_xa      varchar,\n" +
                "\t a_g_c_gy      varchar,\n" +
                "\t a_g_c_zz      varchar,\n" +
                "\t a_g_c_cq      varchar,\n" +
                "\t a_g_p_nmg     varchar,\n" +
                "\t a_g_p_bjs     varchar,\n" +
                "\t a_g_p_jlp     varchar,\n" +
                "\t a_g_p_ajly    varchar,\n" +
                "\t a_g_p_nxhz    varchar,\n" +
                "\t a_g_p_sxp     varchar,\n" +
                "\t a_g_p_lnp     varchar,\n" +
                "\t a_g_p_sh      varchar,\n" +
                "\t a_g_p_yn      varchar,\n" +
                "\t a_g_p_sc      varchar,\n" +
                "\t a_g_p_tj      varchar,\n" +
                "\t a_g_p_ah      varchar,\n" +
                "\t a_g_p_sd      varchar,\n" +
                "\t a_g_p_gd      varchar,\n" +
                "\t a_g_p_gx      varchar,\n" +
                "\t a_g_p_js      varchar,\n" +
                "\t a_g_p_jx      varchar,\n" +
                "\t a_g_p_hb      varchar,\n" +
                "\t a_g_p_hn      varchar,\n" +
                "\t a_g_p_zj      varchar,\n" +
                "\t a_g_p_hbs     varchar,\n" +
                "\t a_g_p_hns     varchar,\n" +
                "\t a_g_p_gs      varchar,\n" +
                "\t a_g_p_fj      varchar,\n" +
                "\t a_g_p_gz      varchar,\n" +
                "\t a_g_p_cq      varchar,\n" +
                "\t a_g_p_sx      varchar,\n" +
                "\t a_g_p_hlj     varchar\n" +
                "  )WITH(\n" +
                "     type ='kafka',\n" +
                "     bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "     topic ='realtime_risk_ctrl_tag',\n" +
                "     parallelism ='1'\n" +
                "  ); \n" +
                "\n" +
                " CREATE TABLE adl_bd_ls_risk_control_middle_table (\n" +
                "    info:suid     varchar as suid,\n" +
                "    PRIMARY KEY (customer_id), \n" +
                "    PERIOD FOR SYSTEM_TIME \n" +
                ") WITH (\n" +
                "   type = 'hbase',\n" +
                "   zookeeperQuorum = '10.201.7.113:2181,10.201.7.114:2181,10.201.7.115:2181',\n" +
                "   zookeeperParent = '/hbase',\n" +
                "   tableName = 'adl_bd_ls_risk_control_middle_table');\n" +
                " \n" +
                " \n" +
                " CREATE TABLE adl_bd_ls_risk_control_middle_table1 (\n" +
                "    info:register_time varchar as register_time ,\n" +
                "    PRIMARY KEY (suid_md5), \n" +
                "    PERIOD FOR SYSTEM_TIME \n" +
                ") WITH (\n" +
                "   type = 'hbase',\n" +
                "   zookeeperQuorum = '10.201.7.113:2181,10.201.7.114:2181,10.201.7.115:2181',\n" +
                "   zookeeperParent = '/hbase',\n" +
                "   tableName = 'adl_bd_ls_risk_control_middle_table');\n" +
                " \n" +
                "  \n" +
                "CREATE TABLE tag2(\n" +
                "     rowkey        varchar,\n" +
                "     cf            varchar,\n" +
                "     hbase_table   varchar,\n" +
                "     suid          varchar,\n" +
                "\t e_b           varchar,\n" +
                "\t e_c           varchar,\n" +
                "\t e_d           varchar,\n" +
                "\t e_e           varchar,\n" +
                "\t e_f           varchar,\n" +
                "\t e_g           varchar,\n" +
                "\t e_h           varchar,\n" +
                "\t e_i           varchar\n" +
                "  )WITH(\n" +
                "     type ='kafka',\n" +
                "     bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "     topic ='realtime_risk_ctrl_tag',\n" +
                "     parallelism ='1'\n" +
                "  );  \n" +
                "\n" +
                "CREATE TABLE tag3(\n" +
                "     rowkey                  varchar,\n" +
                "     cf                      varchar,\n" +
                "     hbase_table             varchar,\n" +
                "     serial_no               varchar,\n" +
                "\t app_mass_high_num_m1    varchar,\n" +
                "\t app_mass_high_num_m3    varchar,\n" +
                "\t app_mass_high_num_m12   varchar,\n" +
                "\t app_use_months_m6       varchar,\n" +
                "\t app_use_num_m1          varchar,\n" +
                "\t app_use_num_m3          varchar,\n" +
                "\t app_use_num_m6          varchar,\n" +
                "\t app_use_num_m12         varchar\t \n" +
                "  )WITH(\n" +
                "     type ='kafka',\n" +
                "     bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "     topic ='realtime_risk_ctrl_tag',\n" +
                "     parallelism ='1'\n" +
                "  );   \n" +
                " \n" +
                " \n" +
                " CREATE TABLE tag4(\n" +
                "     rowkey        varchar,\n" +
                "     cf            varchar,\n" +
                "     hbase_table   varchar,\n" +
                "     serial_no     varchar,\n" +
                "\t bj_hyscore    varchar\n" +
                "  )WITH(\n" +
                "     type ='kafka',\n" +
                "     bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "     topic ='realtime_risk_ctrl_tag',\n" +
                "     parallelism ='1'\n" +
                "  ); \n" +
                "  \n" +
                "CREATE TABLE tag5(\n" +
                "     rowkey        varchar,\n" +
                "     cf            varchar,\n" +
                "     hbase_table   varchar,\n" +
                "     serial_no     varchar,\n" +
                "\t tx_score1age  varchar,\n" +
                "\t tx_score2     varchar,\n" +
                "\t tx_score3     varchar,\n" +
                "\t tx_score4     varchar,\n" +
                "\t tx_score5     varchar,\n" +
                "\t tx_score6     varchar\n" +
                "  )WITH(\n" +
                "     type ='kafka',\n" +
                "     bootstrapServers ='10.201.7.187:9093,10.201.7.188:9093,10.201.7.189:9093,10.201.7.190:9093',\n" +
                "     topic ='realtime_risk_ctrl_tag',\n" +
                "     parallelism ='1'\n" +
                "  );   \n" +
                "  \n" +
                "\n" +
                "\n" +
                "insert into tag2\n" +
                "select \n" +
                "   md5(m2.suid) as rowkey,\n" +
                "   'info' as cf,  \n" +
                "   'adl_bd_ls_apply_score_card_v2' as hbase_table,   \n" +
                "   m2.suid as suid,\n" +
                "   case when m1.edu_level ='2'    then '1'  else '0' end as e_b,\n" +
                "   case when m1.edu_level ='3'    then '1'  else '0' end as e_c,\n" +
                "   case when m1.edu_level ='4'    then '1'  else '0' end as e_d,\n" +
                "   case when m1.edu_level ='5'    then '1'  else '0' end as e_e,\n" +
                "   case when m1.edu_level ='6'    then '1'  else '0' end as e_f,\n" +
                "   case when m1.edu_level ='7'    then '1'  else '0' end as e_g,\n" +
                "   case when m1.edu_level ='8'    then '1'  else '0' end as e_h,\n" +
                "   case when m1.edu_level ='9'    then '1'  else '0' end as e_i\n" +
                "from (select  md5(customer_id) as customer_id,edu_level\n" +
                "     from bdl_suigo_loan_customer_per_base_info\n" +
                "\t where NotBlank(customer_id) AND NotBlank(edu_level) ) m1\n" +
                "join adl_bd_ls_risk_control_middle_table m2\n" +
                "on m1.customer_id = m2.customer_id;\n" +
                "\n" +
                "\n" +
                "\n" +
                "insert into tag3\n" +
                "select \n" +
                "    md5(serial_no) as rowkey,\n" +
                "   'info' as cf,  \n" +
                "   'adl_bd_ls_apply_score_card_v2' as hbase_table,   \n" +
                "   serial_no,\n" +
                "   app_mass_high_num_m1,\n" +
                "   app_mass_high_num_m3,\n" +
                "   app_mass_high_num_m12,\n" +
                "   app_use_months_m6,\n" +
                "   app_use_num_m1,\n" +
                "   app_use_num_m3,\n" +
                "   app_use_num_m6,\n" +
                "   app_use_num_m12\n" +
                "from  \n" +
                "   bdl_nova_t_struct_bingjian_multi\n" +
                "where NotBlank(serial_no);\n" +
                "   \n" +
                "insert into tag4   \n" +
                "select  \n" +
                "    md5(serial_no) as rowkey,\n" +
                "   'info' as cf,  \n" +
                "   'adl_bd_ls_apply_score_card_v2' as hbase_table,   \n" +
                "   serial_no,\n" +
                "   bj_hyscore\n" +
                "from \n" +
                "   bdl_nova_t_struct_bingjian_clever_eyes_score\n" +
                "where NotBlank(serial_no);\n" +
                "\n" +
                "insert into tag5    \n" +
                "select\n" +
                "    md5(serial_no) as rowkey,\n" +
                "   'info' as cf,  \n" +
                "   'adl_bd_ls_apply_score_card_v2' as hbase_table,   \n" +
                "    serial_no, \n" +
                "    tx_score1,\n" +
                "\ttx_score2,\n" +
                "\ttx_score3,\n" +
                "\ttx_score4,\n" +
                "\ttx_score5,\n" +
                "\ttx_score6\n" +
                "from \n" +
                "    bdl_nova_t_struct_tencent_antifraud\n" +
                "where NotBlank(serial_no);\t\n" +
                "\t\n" +
                " \n" +
                "  \n" +
                "  \n" +
                "insert into taga  \n" +
                "select\n" +
                "   md5(t1.suid) as rowkey,\n" +
                "   'info' as cf,  \n" +
                "   'adl_bd_ls_apply_score_card_v2' as hbase_table,   \n" +
                "   t1.suid,\n" +
                "   cast(substring(cast(CURRENT_DATE as varchar),0,4) as int) - cast (substring(t1.id_num,7,4) as int)  as age,\n" +
                "   case  when substring(t1.id_num,-2,1) in (0,2,4,6,8) then '1'  else '0' end  as sex,\n" +
                "   case  when  t1.actived_time > t2.register_time then '1' else '0' end as ssj_u,\n" +
                "   case  when  t1.active_gps_city = '七台河市'               then '1' else '0' end as a_g_c_qth,\n" +
                "   case  when  t1.active_gps_city = '三明市'                 then '1' else '0' end as a_g_c_sm, \n" +
                "   case  when  t1.active_gps_city = '东营市'                 then '1' else '0' end as a_g_c_dys,\n" +
                "   case  when  t1.active_gps_city = '中山市'                 then '1' else '0' end as a_g_c_zs,\n" +
                "   case  when  t1.active_gps_city = '佛山市'                 then '1' else '0' end as a_g_c_fs,\n" +
                "   case  when  t1.active_gps_city = '保定市'                 then '1' else '0' end as a_g_c_bd,\n" +
                "   case  when  t1.active_gps_city = '北京市'                 then '1' else '0' end as a_g_c_bj,\n" +
                "   case  when  t1.active_gps_city = '南京市'                 then '1' else '0' end as a_g_c_nj,\n" +
                "   case  when  t1.active_gps_city = '南宁市'                 then '1' else '0' end as a_g_c_pn,\n" +
                "   case  when  t1.active_gps_city = '南平市'                 then '1' else '0' end as a_g_c_np,\n" +
                "   case  when  t1.active_gps_city = '南昌市'                 then '1' else '0' end as a_g_c_nc,\n" +
                "   case  when  t1.active_gps_city = '南通市'                 then '1' else '0' end as a_g_c_nt,\n" +
                "   case  when  t1.active_gps_city = '厦门市'                 then '1' else '0' end as a_g_c_xm,\n" +
                "   case  when  t1.active_gps_city = '台州市'                 then '1' else '0' end as a_g_c_tzs,\n" +
                "   case  when  t1.active_gps_city = '吉林市'                 then '1' else '0' end as a_g_c_jl,\n" +
                "   case  when  t1.active_gps_city = '吴忠市'                 then '1' else '0' end as a_g_c_wzs,\n" +
                "   case  when  t1.active_gps_city = '呼伦贝尔市'             then '1' else '0' end as a_g_c_hlbe,\n" +
                "   case  when  t1.active_gps_city = '咸宁市'                 then '1' else '0' end as a_g_c_xn,\n" +
                "   case  when  t1.active_gps_city = '嘉兴市'                 then '1' else '0' end as a_g_c_jx,\n" +
                "   case  when  t1.active_gps_city = '大理白族自治州'         then '1' else '0' end as a_g_c_dlbz,\n" +
                "   case  when  t1.active_gps_city = '大连市'                 then '1' else '0' end as a_g_c_dl,\n" +
                "   case  when  t1.active_gps_city = '太原市'                 then '1' else '0' end as a_g_c_ty,\n" +
                "   case  when  t1.active_gps_city = '宁波市'                 then '1' else '0' end as a_g_c_nb,\n" +
                "   case  when  t1.active_gps_city = '宜昌市'                 then '1' else '0' end as a_g_c_yc,\n" +
                "   case  when  t1.active_gps_city = '岳阳市'                 then '1' else '0' end as a_g_c_yy,\n" +
                "   case  when  t1.active_gps_city = '常州市'                 then '1' else '0' end as a_g_c_cz,\n" +
                "   case  when  t1.active_gps_city = '廊坊市'                 then '1' else '0' end as a_g_c_lf,\n" +
                "   case  when  t1.active_gps_city = '德阳市'                 then '1' else '0' end as a_g_c_dy,\n" +
                "   case  when  t1.active_gps_city = '扬州市'                 then '1' else '0' end as a_g_c_yz,\n" +
                "   case  when  t1.active_gps_city = '揭阳市'                 then '1' else '0' end as a_g_c_jys,\n" +
                "   case  when  t1.active_gps_city = '无锡市'                 then '1' else '0' end as a_g_c_wx,\n" +
                "   case  when  t1.active_gps_city = '杭州市'                 then '1' else '0' end as a_g_c_hz,\n" +
                "   case  when  t1.active_gps_city = '柳州市'                 then '1' else '0' end as a_g_c_lz,\n" +
                "   case  when  t1.active_gps_city = '武汉市'                 then '1' else '0' end as a_g_c_wh,\n" +
                "   case  when  t1.active_gps_city = '沈阳市'                 then '1' else '0' end as a_g_c_sy,\n" +
                "   case  when  t1.active_gps_city = '泉州市'                 then '1' else '0' end as a_g_c_qz,\n" +
                "   case  when  t1.active_gps_city = '泰安市'                 then '1' else '0' end as a_g_c_ta,\n" +
                "   case  when  t1.active_gps_city = '泰州市'                 then '1' else '0' end as a_g_c_tz,\n" +
                "   case  when  t1.active_gps_city = '洛阳市'                 then '1' else '0' end as a_g_c_ly,\n" +
                "   case  when  t1.active_gps_city = '济南市'                 then '1' else '0' end as a_g_c_jn,\n" +
                "   case  when  t1.active_gps_city = '济源市'                 then '1' else '0' end as a_g_c_jy,\n" +
                "   case  when  t1.active_gps_city = '海口市'                 then '1' else '0' end as a_g_c_hk,\n" +
                "   case  when  t1.active_gps_city = '淄博市'                 then '1' else '0' end as a_g_c_zb,\n" +
                "   case  when  t1.active_gps_city = '淮北市'                 then '1' else '0' end as a_g_c_hbss,\n" +
                "   case  when  t1.active_gps_city = '渭南市'                 then '1' else '0' end as a_g_c_wn,\n" +
                "   case  when  t1.active_gps_city = '湖州市'                 then '1' else '0' end as a_g_c_hzs,\n" +
                "   case  when  t1.active_gps_city = '湘潭市'                 then '1' else '0' end as a_g_c_xts,\n" +
                "   case  when  t1.active_gps_city = '湘西土家族苗族自治州'   then '1' else '0' end as a_g_c_xxtj,\n" +
                "   case  when  t1.active_gps_city = '湛江市'                 then '1' else '0' end as a_g_c_zjss,\n" +
                "   case  when  t1.active_gps_city = '滁州市'                 then '1' else '0' end as a_g_c_xzs,\n" +
                "   case  when  t1.active_gps_city = '滨州市'                 then '1' else '0' end as a_g_c_bzs,\n" +
                "   case  when  t1.active_gps_city = '漳州市'                 then '1' else '0' end as a_g_c_zzss\n" +
                " from (select\n" +
                "   suid ,\n" +
                "   md5(suid) as suid_md5,     \n" +
                "   FinAesDecode(id_num,'ed5e62c607c7c2e441817518ba9ad16bc635aaf93146c5fd3cf8985bf858f0ce') as id_num,\n" +
                "   actived_time,\n" +
                "   active_gps_city,\n" +
                "   active_gps_prov \n" +
                "from  bdl_suigo_loan_customer_app_active\n" +
                "where NotBlank(suid) ) t1 \n" +
                "left join adl_bd_ls_risk_control_middle_table1 t2 on t1.suid_md5 = t2.suid_md5 ;   \n" +
                "  \n" +
                "insert into tagb  \n" +
                "select\n" +
                "   md5(t1.suid) as rowkey,\n" +
                "   'info' as cf,  \n" +
                "   'adl_bd_ls_apply_score_card_v2' as hbase_table,   \n" +
                "   t1.suid,   \n" +
                "   case  when  t1.active_gps_city = '潍坊市'                 then '1' else '0' end as a_g_c_wfs,\n" +
                "   case  when  t1.active_gps_city = '潮州市'                 then '1' else '0' end as a_g_c_czss,\n" +
                "   case  when  t1.active_gps_city = '濮阳市'                 then '1' else '0' end as a_g_c_pys,\n" +
                "   case  when  t1.active_gps_city = '烟台市'                 then '1' else '0' end as a_g_c_ytss,\n" +
                "   case  when  t1.active_gps_city = '牡丹江市'               then '1' else '0' end as a_g_c_mdj,\n" +
                "   case  when  t1.active_gps_city = '珠海市'                 then '1' else '0' end as a_g_c_zhs,\n" +
                "   case  when  t1.active_gps_city = '茂名市'                 then '1' else '0' end as a_g_c_mms,\n" +
                "   case  when  t1.active_gps_city = '衡阳市'                 then '1' else '0' end as a_g_c_hyss,\n" +
                "   case  when  t1.active_gps_city = '资阳市'                 then '1' else '0' end as a_g_c_zyss,\n" +
                "   case  when  t1.active_gps_city = '赣州市'                 then '1' else '0' end as a_g_c_gzs,\n" +
                "   case  when  t1.active_gps_city = '迪庆藏族自治州'         then '1' else '0' end as a_g_c_dqzz,\n" +
                "   case  when  t1.active_gps_city = '遂宁市'                 then '1' else '0' end as a_g_c_sns,\n" +
                "   case  when  t1.active_gps_city = '遵义市'                 then '1' else '0' end as a_g_c_zys,\n" +
                "   case  when  t1.active_gps_city = '邵阳市'                 then '1' else '0' end as a_g_c_sys,\n" +
                "   case  when  t1.active_gps_city = '郴州市'                 then '1' else '0' end as a_g_c_zzs,\n" +
                "   case  when  t1.active_gps_city = '鄂尔多斯市'             then '1' else '0' end as a_g_c_eeds,\n" +
                "   case  when  t1.active_gps_city = '酒泉市'                 then '1' else '0' end as a_g_c_jqs,\n" +
                "   case  when  t1.active_gps_city = '金华市'                 then '1' else '0' end as a_g_c_jhs,\n" +
                "   case  when  t1.active_gps_city = '金边'                   then '1' else '0' end as a_g_c_jb,\n" +
                "   case  when  t1.active_gps_city = '钦州市'                 then '1' else '0' end as a_g_c_qzs,\n" +
                "   case  when  t1.active_gps_city = '铁岭市'                 then '1' else '0' end as a_g_c_tls,\n" +
                "   case  when  t1.active_gps_city = '铜仁市'                 then '1' else '0' end as a_g_c_trs,\n" +
                "   case  when  t1.active_gps_city = '银川市'                 then '1' else '0' end as a_g_c_ycs,\n" +
                "   case  when  t1.active_gps_city = '锡林郭勒盟'             then '1' else '0' end as a_g_c_xls,\n" +
                "   case  when  t1.active_gps_city = '锦州市'                 then '1' else '0' end as a_g_c_jxss,\n" +
                "   case  when  t1.active_gps_city = '镇江市'                 then '1' else '0' end as a_g_c_zjs,\n" +
                "   case  when  t1.active_gps_city = '长春市'                 then '1' else '0' end as a_g_c_ccs,\n" +
                "   case  when  t1.active_gps_city = '长沙市'                 then '1' else '0' end as a_g_c_cc,\n" +
                "   case  when  t1.active_gps_city = '长治市'                 then '1' else '0' end as a_g_c_czs,\n" +
                "   case  when  t1.active_gps_city = '阜新市'                 then '1' else '0' end as a_g_c_hxx,\n" +
                "   case  when  t1.active_gps_city = '阜阳市'                 then '1' else '0' end as a_g_c_hys,\n" +
                "   case  when  t1.active_gps_city = '防城港市'               then '1' else '0' end as a_g_c_fcg,\n" +
                "   case  when  t1.active_gps_city = '阳江市'                 then '1' else '0' end as a_g_c_yjs,\n" +
                "   case  when  t1.active_gps_city = '阳泉市'                 then '1' else '0' end as a_g_c_yqs,\n" +
                "   case  when  t1.active_gps_city = '阿坝藏族羌族自治州'     then '1' else '0' end as a_g_c_abzz,\n" +
                "   case  when  t1.active_gps_city = '阿拉善盟'               then '1' else '0' end as a_g_c_alsm,\n" +
                "   case  when  t1.active_gps_city = '陇南市'                 then '1' else '0' end as a_g_c_lns,\n" +
                "   case  when  t1.active_gps_city = '随州市'                 then '1' else '0' end as a_g_c_szss,\n" +
                "   case  when  t1.active_gps_city = '雅安市'                 then '1' else '0' end as a_g_c_yas,\n" +
                "   case  when  t1.active_gps_city = '青岛市'                 then '1' else '0' end as a_g_c_qds,\n" +
                "   case  when  t1.active_gps_city = '鞍山市'                 then '1' else '0' end as a_g_c_ass,\n" +
                "   case  when  t1.active_gps_city = '韶关市'                 then '1' else '0' end as a_g_c_sgs,\n" +
                "   case  when  t1.active_gps_city = '马鞍山市'               then '1' else '0' end as a_g_c_mass,\n" +
                "   case  when  t1.active_gps_city = '驻马店市'               then '1' else '0' end as a_g_c_zmds,\n" +
                "   case  when  t1.active_gps_city = '鸡西市'                 then '1' else '0' end as a_g_c_jxs,\n" +
                "   case  when  t1.active_gps_city = '鹤壁市'                 then '1' else '0' end as a_g_c_hbsss,\n" +
                "   case  when  t1.active_gps_city = '鹤岗市'                 then '1' else '0' end as a_g_c_hgss,\n" +
                "   case  when  t1.active_gps_city = '鹰潭市'                 then '1' else '0' end as a_g_c_yts  \n" +
                "from  bdl_suigo_loan_customer_app_active t1\n" +
                "where NotBlank(suid);   \n" +
                "   \n" +
                "insert into tagc  \n" +
                "select\n" +
                "   md5(t1.suid) as rowkey,\n" +
                "   'info' as cf,  \n" +
                "   'adl_bd_ls_apply_score_card_v2' as hbase_table,   \n" +
                "   t1.suid,                                                         \n" +
                "   case  when  t1.active_gps_city = '黄冈市'                 then '1' else '0' end as a_g_c_hgs,\n" +
                "   case  when  t1.active_gps_city = '黄山市'                 then '1' else '0' end as a_g_c_hss,\n" +
                "   case  when  t1.active_gps_city = '黄石市'                 then '1' else '0' end as a_g_c_hsss,\n" +
                "   case  when  t1.active_gps_city = '黑河市'                 then '1' else '0' end as a_g_c_hhs,\n" +
                "   case  when  t1.active_gps_city = '黔东南苗族侗族自治州'   then '1' else '0' end as a_g_c_qdnmz,\n" +
                "   case  when  t1.active_gps_city = '黔南布依族苗族自治州'   then '1' else '0' end as a_g_c_qdnyz,\n" +
                "   case  when  t1.active_gps_city = '黔西南布依族苗族自治州' then '1' else '0' end as a_g_c_qxnby,\n" +
                "   case  when  t1.active_gps_city = '齐齐哈尔市'             then '1' else '0' end as a_g_c_qqhe,\n" +
                "   case  when  t1.active_gps_city = '龙岩市'                 then '1' else '0' end as a_g_c_lys,\n" +
                "   case  when  t1.active_gps_city = '上海市'                 then '1' else '0' end as a_g_c_sh,\n" +
                "   case  when  t1.active_gps_city = '东莞市'                 then '1' else '0' end as a_g_c_dg,\n" +
                "   case  when  t1.active_gps_city = '南京市'                 then '1' else '0' end as a_g_c_nj,\n" +
                "   case  when  t1.active_gps_city = '合肥市'                 then '1' else '0' end as a_g_c_hf,\n" +
                "   case  when  t1.active_gps_city = '天津市'                 then '1' else '0' end as a_g_c_tj,\n" +
                "   case  when  t1.active_gps_city = '广州市'                 then '1' else '0' end as a_g_c_gz,\n" +
                "   case  when  t1.active_gps_city = '成都市'                 then '1' else '0' end as a_g_c_cd,\n" +
                "   case  when  t1.active_gps_city = '昆明市'                 then '1' else '0' end as a_g_c_km,\n" +
                "   case  when  t1.active_gps_city = '深圳市'                 then '1' else '0' end as a_g_c_sz,\n" +
                "   case  when  t1.active_gps_city = '温州市'                 then '1' else '0' end as a_g_c_wz,\n" +
                "   case  when  t1.active_gps_city = '石家庄市'               then '1' else '0' end as a_g_c_sjz,\n" +
                "   case  when  t1.active_gps_city = '福州市'                 then '1' else '0' end as a_g_c_fz,\n" +
                "   case  when  t1.active_gps_city = '苏州市'                 then '1' else '0' end as a_g_c_szs,\n" +
                "   case  when  t1.active_gps_city = '西安市'                 then '1' else '0' end as a_g_c_xa,\n" +
                "   case  when  t1.active_gps_city = '贵阳市'                 then '1' else '0' end as a_g_c_gy,\n" +
                "   case  when  t1.active_gps_city = '郑州市'                 then '1' else '0' end as a_g_c_zz,\n" +
                "   case  when  t1.active_gps_city = '重庆市'                 then '1' else '0' end as a_g_c_cq,\n" +
                "   case  when  t1.active_gps_prov = '内蒙古自治区'           then '1' else '0' end as a_g_p_nmg,\n" +
                "   case  when  t1.active_gps_prov = '北京市'                 then '1' else '0' end as a_g_p_bjs,\n" +
                "   case  when  t1.active_gps_prov = '吉林省'                 then '1' else '0' end as a_g_p_jlp,\n" +
                "   case  when  t1.active_gps_prov = '埃杰里亚[临时]'         then '1' else '0' end as a_g_p_ajly,\n" +
                "   case  when  t1.active_gps_prov = '宁夏回族自治区'         then '1' else '0' end as a_g_p_nxhz,\n" +
                "   case  when  t1.active_gps_prov = '山西省'                 then '1' else '0' end as a_g_p_sxp,\n" +
                "   case  when  t1.active_gps_prov = '辽宁省'                 then '1' else '0' end as a_g_p_lnp,\n" +
                "   case  when  t1.active_gps_prov = '上海市'                 then '1' else '0' end as a_g_p_sh,\n" +
                "   case  when  t1.active_gps_prov = '云南省'                 then '1' else '0' end as a_g_p_yn,\n" +
                "   case  when  t1.active_gps_prov = '四川省'                 then '1' else '0' end as a_g_p_sc,\n" +
                "   case  when  t1.active_gps_prov = '天津市'                 then '1' else '0' end as a_g_p_tj,\n" +
                "   case  when  t1.active_gps_prov = '安徽省'                 then '1' else '0' end as a_g_p_ah,\n" +
                "   case  when  t1.active_gps_prov = '山东省'                 then '1' else '0' end as a_g_p_sd,\n" +
                "   case  when  t1.active_gps_prov = '广东省'                 then '1' else '0' end as a_g_p_gd,\n" +
                "   case  when  t1.active_gps_prov = '广西壮族自治区'         then '1' else '0' end as a_g_p_gx,\n" +
                "   case  when  t1.active_gps_prov = '江苏省'                 then '1' else '0' end as a_g_p_js,\n" +
                "   case  when  t1.active_gps_prov = '江西省'                 then '1' else '0' end as a_g_p_jx,\n" +
                "   case  when  t1.active_gps_prov = '河北省'                 then '1' else '0' end as a_g_p_hb,\n" +
                "   case  when  t1.active_gps_prov = '河南省'                 then '1' else '0' end as a_g_p_hn,\n" +
                "   case  when  t1.active_gps_prov = '浙江省'                 then '1' else '0' end as a_g_p_zj,\n" +
                "   case  when  t1.active_gps_prov = '湖北省'                 then '1' else '0' end as a_g_p_hbs,\n" +
                "   case  when  t1.active_gps_prov = '湖南省'                 then '1' else '0' end as a_g_p_hns,\n" +
                "   case  when  t1.active_gps_prov = '甘肃省'                 then '1' else '0' end as a_g_p_gs,\n" +
                "   case  when  t1.active_gps_prov = '福建省'                 then '1' else '0' end as a_g_p_fj,\n" +
                "   case  when  t1.active_gps_prov = '贵州省'                 then '1' else '0' end as a_g_p_gz,\n" +
                "   case  when  t1.active_gps_prov = '重庆市'                 then '1' else '0' end as a_g_p_cq,\n" +
                "   case  when  t1.active_gps_prov = '陕西省'                 then '1' else '0' end as a_g_p_sx,\n" +
                "   case  when  t1.active_gps_prov = '黑龙江省'               then '1' else '0' end as a_g_p_hlj\n" +
                "from  bdl_suigo_loan_customer_app_active t1\n" +
                "where NotBlank(suid);  \t\t \n";



        exec(sql);

    }






    public void exec(String sql) throws Exception {
        List<String> paramList = Lists.newArrayList();
        paramList.add("-sql");
        String exeSql = URLEncoder.encode(sql, Charsets.UTF_8.name());
        paramList.add(exeSql);
        paramList.add("-name");
        paramList.add("xc");
        paramList.add("-mode");
        paramList.add("local");
        paramList.add("-port");
        paramList.add("8081");
        paramList.add("-confProp");
        String conf = "{\"time.characteristic\":\"EventTime\",\"parallelism\":1}";
        String confEncode = URLEncoder.encode(conf, Charsets.UTF_8.name());
        paramList.add(confEncode);

        String[] params = new String[paramList.size()];
        paramList.toArray(params);
        Main.main(params);
    }
}
